<?php

namespace app\logic;

vendor('PHPExcel.PHPExcel');
vendor('aliyun-oss-php-sdk-master.autoload');

use app\Param;
use OSS\OssClient;
use OSS\Core\OssException;

class Excel extends Param
{
    private static $obj = null;


    public $payment_code = [
        100 => '支付宝支付',
        200 => '微信支付',
        300 => '微信公众号支付',
        400 => '美田卡支付',
    ];

    public $order_from = [
        'weChat' => '微信公众号',
        'app' => 'app',
    ];

    public $order_state = [
        100 => '待付款',
        200 => '已付款',
        300 => '已发货',
        400 => '已收货',
        0 => '已取消'
    ];

    public $study_order_state = [
        100 => '待付款',
        200 => '已报名',
        300 => '已完成',
        0 => '已取消'
    ];
    public $return_state = [
        100 => '待处理',
        200 => '退货中',
        300 => '已完成',
        0 => '已拒绝'
    ];

    public function _initialize()
    {
        parent::_initialize();
    }

    /**
     * 类入口
     * @return Excel|null
     */
    public static function getInstance()
    {
        if (is_null(self::$obj))
            self::$obj = new self();
        return self::$obj;
    }

    /**
     * 导出excel数据
     * @param $arr_excel array 例：[['a'=>'a1','b'=>'b1'],['a'=>'a2','b'=>'b2']]
     * @param $arr_field array 例：['a'=>'第一列','b'=>'第二列']
     * @param $str_title string
     */
    public function export($arr_excel, $arr_field, $str_title = 'excel')
    {
        set_time_limit(0);
        //创建对象
        $objPHPExcel = new \PHPExcel();

        //设置属性
        $objPHPExcel->getProperties()
            ->setCreator("WOLF")
            ->setLastModifiedBy("WOLF")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");

        $en = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];

        //3.填充表格
        $objActSheet = $objPHPExcel->setActiveSheetIndex(0); //填充表头
        $i = 0;
        foreach ($arr_field as $value) {
            $objActSheet->setCellValue($en[$i] . '1', $value);
            $i++;
        }
        //填充内容
        $k = 2;
        foreach ($arr_excel as $value) {
            $j = 0;
            foreach ($arr_field as $key => $val) {
                $objActSheet->setCellValue($en[$j] . $k, (string)$value[$key]);
                $j++;
            }
            unset($j,$val,$key);
            $k++;
        }

        //4.输出
        $objPHPExcel->getActiveSheet()->setTitle($str_title);
        $objPHPExcel->setActiveSheetIndex(0);

        $filename = $str_title . '.xls';
        ob_end_clean();//清除缓冲区,避免乱码
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename=' . $filename);
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    /**
     * 导出excel数据
     * @param $arr_excel array 例：[['a'=>'a1','b'=>'b1'],['a'=>'a2','b'=>'b2']]
     * @param $arr_field array 例：['a'=>'第一列','b'=>'第二列']
     * @param $str_title string
     */
    public function exportReturn($arr_excel, $str_title = 'excel')
    {
        set_time_limit(0);
        //创建对象
        $objPHPExcel = new \PHPExcel();

        //设置属性
        $objPHPExcel->getProperties()
            ->setCreator("WOLF")
            ->setLastModifiedBy("WOLF")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");

        $en = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];

        //3.填充表格
        $objActSheet = $objPHPExcel->setActiveSheetIndex(0); //填充表头
        $objActSheet->setCellValue('A1', '服务单号');
        $objActSheet->setCellValue('B1', '下单时间');
        $objActSheet->setCellValue('C1', '用户名');
        $objActSheet->setCellValue('D1', '退款金额');
        $objActSheet->setCellValue('E1', '退款状态');
        $objActSheet->setCellValue('F1', '处理时间');


        //填充内容
        $k = 2;
        foreach ($arr_excel as $value) {
            $objActSheet->setCellValue('A' . $k, $value['returnSn']);
            $objActSheet->setCellValue('B' . $k, $value['addTime']);
            $objActSheet->setCellValue('C' . $k, $value['userName']);
            $objActSheet->setCellValue('D' . $k, $value['returnAmount']);
            $objActSheet->setCellValue('E' . $k, $this->return_state[$value['returnState']]);
            $objActSheet->setCellValue('F' . $k, $value['handleTime']);
            $k++;
        }

        //4.输出
        $objPHPExcel->getActiveSheet()->setTitle($str_title);
        $objPHPExcel->setActiveSheetIndex(0);
        $filename = $str_title . '.xls';
        ob_end_clean();//清除缓冲区,避免乱码
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename=' . $filename);
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    /**
     * 导出excel数据
     * @param $arr_excel array 例：[['a'=>'a1','b'=>'b1'],['a'=>'a2','b'=>'b2']]
     * @param $arr_field array 例：['a'=>'第一列','b'=>'第二列']
     * @param $str_title string
     */
    public function exportOrder($arr_excel, $str_title = 'excel')
    {
        set_time_limit(0);
        //创建对象
        $objPHPExcel = new \PHPExcel();

        //设置属性
        $objPHPExcel->getProperties()
            ->setCreator("WOLF")
            ->setLastModifiedBy("WOLF")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");

        $en = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];

        //3.填充表格
        $objActSheet = $objPHPExcel->setActiveSheetIndex(0); //填充表头
        $objActSheet->setCellValue('A1', '订单编号');
        $objActSheet->setCellValue('B1', '订单金额');
        $objActSheet->setCellValue('C1', '下单时间');
        $objActSheet->setCellValue('D1', '支付方式');
        $objActSheet->setCellValue('E1', '订单状态');
        $objActSheet->setCellValue('F1', '订单来源');
        $objActSheet->setCellValue('G1', '姓名');
        $objActSheet->setCellValue('H1', '电话');
        $objActSheet->setCellValue('I1', '省');
        $objActSheet->setCellValue('J1', '市');
        $objActSheet->setCellValue('K1', '区');
        $objActSheet->setCellValue('L1', '详细地址');


        //填充内容
        $k = 2;
        foreach ($arr_excel as $value) {
            $objActSheet->setCellValue('A' . $k, $value['orderSn']);
            $objActSheet->setCellValue('B' . $k, $value['orderAmount']);
            $objActSheet->setCellValue('C' . $k, $value['addTime']);
            $objActSheet->setCellValue('D' . $k, $this->payment_code[$value['paymentCode']]);
            $objActSheet->setCellValue('E' . $k, $this->order_state[$value['orderState']]);
            $objActSheet->setCellValue('F' . $k, $value['orderFrom']);
            $objActSheet->setCellValue('G' . $k, $value['gatherName']);
            $objActSheet->setCellValue('H' . $k, $value['gatherMobile']);
            $objActSheet->setCellValue('I' . $k, $value['gatherProv']);
            $objActSheet->setCellValue('J' . $k, $value['gatherCity']);
            $objActSheet->setCellValue('K' . $k, $value['gatherCounty']);
            $objActSheet->setCellValue('L' . $k, $value['gatherAddress']);

            $k++;
        }

        //4.输出
        $objPHPExcel->getActiveSheet()->setTitle($str_title);
        $objPHPExcel->setActiveSheetIndex(0);
        $filename = $str_title . '.xls';
        ob_end_clean();//清除缓冲区,避免乱码
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename=' . $filename);
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public function excelStudyOrder($arr_excel, $str_title = 'excel')
    {
        set_time_limit(0);
        //创建对象
        $objPHPExcel = new \PHPExcel();

        //设置属性
        $objPHPExcel->getProperties()
            ->setCreator("WOLF")
            ->setLastModifiedBy("WOLF")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");

        //3.填充表格
        $objActSheet = $objPHPExcel->setActiveSheetIndex(0); //填充表头
        $objActSheet->setCellValue('A1', '订单编号');
        $objActSheet->setCellValue('B1', '订单金额');
        $objActSheet->setCellValue('C1', '下单时间');
        $objActSheet->setCellValue('D1', '支付方式');
        $objActSheet->setCellValue('E1', '订单状态');
        $objActSheet->setCellValue('F1', '订单来源');
        $objActSheet->setCellValue('G1', '留学国家');


        //填充内容
        $k = 2;
        foreach ($arr_excel as $value) {
            $objActSheet->setCellValue('A' . $k, $value['orderSn']);
            $objActSheet->setCellValue('B' . $k, $value['orderAmount']);
            $objActSheet->setCellValue('C' . $k, $value['addTime']);
            $objActSheet->setCellValue('D' . $k, $this->payment_code[$value['paymentCode']]);
            $objActSheet->setCellValue('E' . $k, $this->study_order_state[$value['orderState']]);
            $objActSheet->setCellValue('F' . $k, $this->order_from[$value['orderFrom']]);
            $objActSheet->setCellValue('G' . $k, $value['countyName']);
            $k++;
        }

        //4.输出
        $objPHPExcel->getActiveSheet()->setTitle($str_title);
        $objPHPExcel->setActiveSheetIndex(0);
        $filename = $str_title . '.xls';
        ob_end_clean();//清除缓冲区,避免乱码
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename=' . $filename);
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public function inOp()
    {
        if ($_FILES['excel']['type'] != 'application/octet-stream') {
            echo "类型错误";
            die;
        }
        $uploaded_file = $_FILES['excel']['tmp_name'];

        $file_true_name = $_FILES['excel']['name'];
        $move_to_file = time() . rand(1, 1000) . substr($file_true_name, strrpos($file_true_name, "."));

        try {
            $ossClient = new OssClient(config('oss.oss_access_key_id'), config('oss.oss_access_key_secret'), config('oss.oss_endpoint'));  //
            $ossClient->uploadFile(config('oss.oss_bucket'), $move_to_file, $uploaded_file);
        } catch (OssException $e) {
            echo "上传失败";
            die;
        }

        $str_file_url = 'http://' . config('oss.oss_bucket') . '.' . config('oss.oss_endpoint') . '/' . $move_to_file;


        $inputFileName = $str_file_url;
        date_default_timezone_set('PRC');
        // 读取excel文件
        try {
            $inputFileType = \PHPExcel_IOFactory::identify($inputFileName);
            $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($inputFileName);
        } catch (\Exception $e) {
            die('加载文件发生错误：”' . pathinfo($inputFileName, PATHINFO_BASENAME) . '”: ' . $e->getMessage());
        }

        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();

        for ($row = 3; $row <= $highestRow; $row++) {
            $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
        }
        echo "OK";
    }
}
